Data Sources

Data Pipeline Studio supports a variety of data sources that can be used for data ingestion. Each data source has specific configuration requirements. The section below lists the various types of data sources and their configuration details. Click a data source to view the details.

  1. Click the Salesforce node.

  2. Click the pencil icon to edit the name of the Salesforce node. The node appears in the pipeline with the provided name.

  3. In the Datastore drop down, select a configured Salesforce instance.

  4. Select one or more objects and click Save.

  1. Click the ServiceNow node.

  2. Click the pencil icon to edit the name of the ServiceNow node. The node appears in the pipeline with the provided name.

  3. In the Datastore drop down, select a configured ServiceNow instance.

  4. Select one or more objects and click Save.

  1. Click the CSV node.

  1. Depending on the type of datastore that you select, provide the following information:

    • Browse to locate the CSV file.

    • Select the storage type. This is where the data from the CSV will be stored.

      • DBFS - Databricks File System (This is the default option.)

        • Select the configured instance from the dropdown list.

        • Upload File Folder Path - Select a folder and click Upload.

        • Select a delimiter and enable Header.

      • Amazon S3

        • Select the configured instance from the dropdown list.

        • Upload File Folder Path - Click Add Folder Path. Do one of he following:

          • Click the radio button to select a folder.

          • Click a folder and then select a sub-folder. Click Select.

        • Click Upload.

        • Select a delimiter and enable Header.

    1. Select a Catalog.

    2. Select a Catalog Schema.

    3. Select the Tables from the selected schema. If the source schema changes, then you must run the data crawler and create a new catalog to fetch the latest schema.

  2. Click Save.

  1. Select a Catalog.

  2. Select a Catalog Schema.

  3. Select the Tables from the selected schema. If the source schema changes, then you must run the data crawler and create a new catalog to fetch the latest schema.

  4. Click Save.

  1. Click the MS Excel node.

  1. Depending on the type of datastore that you select, provide the following information:

    • DBFS - Databricks File System (This is the default option.)

      • Select the configured instance from the dropdown list.

      • Upload File Folder Path - Select a folder and click Upload.

      • Select a delimiter and enable Header.

    • Amazon S3

      • Select the configured instance from the dropdown list.

      • Upload File Folder Path - Click Add Folder Path. Do one of he following:

        • Click the radio button to select a folder.

        • Click a folder and then select a sub-folder. Click Select.

      • Click Upload.

      • Select a delimiter and enable Header.

    1. Select a Catalog.

    2. Select a Catalog Schema.

    3. Select the Tables from the selected schema. If the source schema changes, then you must run the data crawler and create a new catalog to fetch the latest schema.

    4. Click Save.

  1. Click the Parquet node.

  2. Depending on the type of datastore that you select, provide the following information:

    • DBFS - Databricks File System (This is the default option.)

      • Select the configured instance from the dropdown list.

      • Upload File Folder Path - Select a folder and click Upload.

      • Select a delimiter and enable Header.

    • Amazon S3

      • Select the configured instance from the dropdown list.

      • Upload File Folder Path - Click Add Folder Path. Do one of he following:

        • Click a radio button to select a folder.

        • Click a folder and then click a radio button to select a sub-folder. Click Select.

      • Click Upload.

      • Select a delimiter and enable Header.

    1. Select a Catalog.

    2. Select a Catalog Schema.

    3. Select the Tables from the selected schema. If the source schema changes, then you must run the data crawler and create a new catalog to fetch the latest schema.

    4. Click Save.

FTP is a data file server. To use FTP as a data source in DPS, you must configure it through Configuration > Platform Setup > Cloud Platform Tools and Technologies. Once that is done, you can configure a node with FTP as a data source.

  1. Click the FTP node.

  2. Depending on the type of datastore that you select, provide the following information:

    1. Select a datastore from the list of configured FTP servers.

    2. Select Path - Click Browse and do one of the following:

      • Click a radio button to select a folder and then click Select.

      • Click a folder and then click a radio button to select a sub-folder. Click Select.

      Note:

      The files in the selected folders must have the same schema, else the job run will fail.

    3. Select the required file format from the following options:

      • CSV

      • JSON

      • Parquet

      • XLSX

      • XML

    4. To select the required files, do one of the following:

      • Select File - choose this option to select the required file.

        • Click Add File and select the required file, then click Select.

        • Select the delimiter.

        • Enable Header if you want the data to include a header.

        • Click Save.

      • Select Filter - choose this option to filter the required files.

        • Files to include - provide a list of files that you want to include from the source. This step is optional

        • Files to exclude - provide a list of files that you want to exclude from the source. This step is optional.

          Note:

          The selected files must have the same schema, else the job run will fail.

        • Select the delimiter.

        • Enable Header if you want the data to include a header.

        • Click Save.

    3. Select New Datastore and provide the required information.

SFTP is a data file server. To use SFTP as a data source in DPS, you must configure it through Configuration > Platform Setup > Cloud Platform Tools and Technologies. Once that is done, you can configure a node with SFTP as a data source. Using SFTP as a data source you can ingest data either into an S3 data lake or a Snowflake data lake depending on your usecase.

Data Ingestion from SFTP to Snowflake

  1. Click the SFTP node.

  2. Depending on the type of datastore that you select, provide the following information:

    1. Select a datastore from the list of configured FTP servers.

    2. Select Path - Click Browse and do one of the following:

      • Click a radio button to select a folder.

      • Click a folder and then click a radio button to select a sub-folder. Click Select.

      When you select a folder, all the files in that folder are used for data ingestion. If you want to use specific files, then use the Select File or Select Filter options mentioned in step 4.

      Note:

      The files in the selected folders must have the same schema, else the job run will fail.

    3. Select the required file format from the following options:

      • CSV

      • JSON

      • Parquet

      • XLSX

      • XML

    4. To select specific files from the folder, choose one of the following options:

      • Select File - Click Add File, browse to the file and click the radio button and click Select.

        • Select the delimiter.

        • Enable Header if you want the data to include a header.

      • Select Filter

        • Files to include - provide a list of files to be included. This is optional.

        • Files to exclude - provide a list of files to be excluded. This is optional.

        Note:

        The selected files must have the same schema, else the job run will fail.

    5. Click Save.

    Select New Datastore and provide the required information.

To use Amazon S3 as a data source, you must configure it through Configuration > Platform Setup > Cloud Platform Tools and Technologies. Once that is done, you can configure an Amazon S3 node as a data source.

  1. Click the Amazon S3 node.

  2. Click the pencil icon to edit the name of the Amazon S3 node. The node appears in the pipeline with the provided name.

  3. Select a datastore from the dropdown.

  4. Select Path - Click Browse and do one of the following:

    • Click a radio button to select a folder.

    • Click a folder and then click a radio button to select a sub-folder. Click Select.

    When you select a folder, all the files in that folder are used for data ingestion. If you want to use selected files, then use the Select File or Select Filter options mentioned in step 6.

    Note:

    The files in the selected folders must have the same schema, else the job run will fail.

  5. Select the required file format from the following options:

    • CSV

    • JSON

    • Parquet

    • XLSX

    • XML

  6. Select one of the following options:

    • Select File - Click Add File, browse to the file and click the radio button and click Select.

      • Select the delimiter.

      • Enable Header if you want the data to include a header.

    • Select Filter

      • Files to include - provide a list of files to be included. This is optional.

      • Files to exclude - provide a list of files to be excluded. This is optional.

      Note:

      The selected files must have the same schema, else the job run will fail.

  1. Click Save.

To use Lazsa Data Ingestion Catalog as a data source, you must first add a data crawler and then associate the data crawler with a data catalog. For more information see Data Crawler and Data Catalog

  1. Select a Catalog.

  2. Select a Catalog Schema.

  3. Select the Tables from the selected schema. If the source schema changes, then you must run the data crawler and create a new catalog to fetch the latest schema.

  4. Click Save.

  1. Click the REST API node.

  2. Provide the following information to configure the REST API node.

    Field Description
    Method Select the GET or POST method.
    URL Provide the URL for the API.
    Response Format Select the format in which you want the API response, choose between JSON and XML.
    Connect using Lazsa Orchestrator Agent

    If you enable Connect using Lazsa Orchestrator Agent, then provide the following information:

    • Select AWS Secrets Manager as the Identity Security Provider.

    • Provide the Key, Secret Name, and Secret Key.
    Request Header Provide the key and value for Request Header. (This is optional).
    Query Parameters

    Add any query parameters according to your requirement for the following:

    1. Initial Load

    2. Incremental Load

    3. Pagination

    Use SSL

    Enable this option to upload an SSL certificate. Select one of the following methods:

    1. Use Existing

      1. Select Storage - Select one of the following options:

        • Databricks File System (DBFS)

        • Amazon S3

      2. Select Configured Datastore - select a datastore from the dropdown list.

    2. Upload New - Click Browse this computer to upload the SSL file. The supported file types are

    Request Body (Optional) Provide the Request Body in JSON format.
    Data Root (Optional)

    Under Data Root add the various data nodes in your REST API output. The data nodes can be directly mapped to target files in a Databricks transformation job.
    Use Custom Proxy

    Select the proxy type:

    • HTTP

    • HTTPS

    Provide the following information:

    • Proxy Server

    • Port

    • Proxy Auth - Enable this option and provide the following information:

      • Username

      • Password

    • Show Preview

  3. Click Save.

  1. Click the Microsoft SQL Server node. Do one of the following:

  • Select Configured Datastore. Choose a datastore from the list of configured datastores. Select one of the following options:

Select a table from the database from which you want data to be fetched. Click Add.

Historical and Incremental configuration

  • Click Expand All. The columns of the tables are displayed. You can filter the data for the columns of the table.

  • Configure Historical - If your table contains historical data for a specific period, you can enable this option to fetch data for a specific date range. Do the following:

    • Select the Start Date and End Date to specify the period for which you want the data to be fetched.

    • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

    • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data.

    • Filter - Select the column for which you want to apply the filter.

  • Configure Incremental - If you want to continue fetching data incrementally, you can enable this option.

    • Start Date - Specify the date from which you want to fetch data incrementally.

    • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

    • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data.

    • Filter - Specify the name of the column for which you want to apply the filter.

    • Additional Filter - Specify any additional column for which you want to fetch the data.

This operation combines the rows from two table references, based on join criteria.

Configure DataStore with Joins option

Provide the following information under Add Table Joins:

  • Primary Table - Select a primary table for the join operation.

  • Primary Table Field - Select the field from the primary table for the join operation.

  • Secondary Table - Select the secondary table for the join operation.

  • Secondary Table Field - Select the field from the secondary table for the join operation.

  • Join Type - Specify the type of join. Select from the options - inner join and outer join.

  • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data. Choose from the options - 2,4,8.

  • Click Add and then click Save.

Provide the custom SQL query that you want to run on the data.

Custom SQL -

Configure Datastore for Custom SQL

  • Provide the custom SQL query that you want to run on the data.

  • Select the number of parallel connections that you want to use to fetch the data. Choose from the options - 2,4,8.

  • Click Save.

  • Select Data Catalog.

    1. Choose the Catalog.

    2. Choose the Catalog Schema.

    3. Click Save.

For more information about Data Catalog, see Data Crawler and Data Catalog

  1. Click the MySQL node.

  2. Use one of the following options to add a datastore:

    • Select Configured Datastore. Choose a datastore from the list of configured datastores. Select one of the following options:

      • Select a table from the dropdown list and click Add.

        Historical and Incremental configuration

      • Click Expand All.

      • Configure Historical - If your table contains historical data for a specific period, you can enable this option to fetch data for a specific date range. Do the following:

        • Select the Start Date and End Date to specify the period for which you want the data to be fetched.

        • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

        • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data.

        • Filter - Select the column for which you want to apply the filter.

      • Configure Incremental - If you want to continue fetching data incrementally, you can enable this option.

        • Start Date - Specify the date from which you want to fetch data incrementally.

        • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

        • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data.

        • Filter - Specify the name of the column for which you want to apply the filter.

        • Additional Filter - Specify any additional column for which you want to fetch the data.

      This operation combines the rows from two table references, based on join criteria.

      Configure DataStore with Joins option

      Provide the following information under Add Table Joins:

      • Primary Table - Select a primary table for the join operation.

      • Primary Table Field - Select the field from the primary table for the join operation.

      • Secondary Table - Select the secondary table for the join operation.

      • Secondary Table Field - Select the field from the secondary table for the join operation.

      • Join Type - Specify the type of join. Select from the options - inner join and outer join.

      • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data. Choose from the options - 2,4,8.

      • Click Add and then click Save.

      Provide the custom SQL query that you want to run on the data.

      Custom SQL - Provide the custom SQL query that you want to run on the data.

      Configure Datastore for Custom SQL

      • Parallel Connections - Select the number of parallel connections that you want to use to fetch the data. Choose from the options - 2,4,8.

      • Click Save.

    • Select New Datastore and provide the required information. See Configure MySQL Connection Details.

    • Select Data Ingestion Catalog.

      Configure datastore for data ingestion catalog

      1. Choose a catalog from the list of configured catalogs.

      2. Choose the Catalog Schema.

      3. Select the Tables from the selected schema. If the source schema changes, then you must run the data crawler and create a new catalog to fetch the latest schema.

      4. Click Save.

For more information about Data Catalog, see Data Crawler and Data Catalog

  1. Click the Oracle node.

  2. Use one of the following options to add a datastore:

    • Select Configured Datastore. Choose a datastore from the list of configured datastores. Select one of the following options:

      Select a table from the database from which you want data to be fetched. Click Add.

      Historical and Incremental configuration

      • Click Expand All.

      • Configure Historical - If your table contains historical data for a specific period, you can enable this option to fetch data for a specific date range. Do the following:

        • Select the Start Date and End Date to specify the period for which you want the data to be fetched.

        • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

        • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data. Choose from the options - 2,4,8.

        • Filter - Select the column for which you want to apply the filter.

      • Configure Incremental - If you want to continue fetching data incrementally, you can enable this option.

        • Start Date - Specify the date from which you want to fetch data incrementally.

        • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

        • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data.

        • Filter - Specify the name of the column for which you want to apply the filter.

        • Additional Filter - Specify any additional column for which you want to fetch the data.

      This operation combines the rows from two table references, based on join criteria.

      Configure DataStore with Joins option

      Provide the following information under Add Table Joins:

      • Primary Table - Select a primary table for the join operation.

      • Primary Table Field - Select the field from the primary table for the join operation.

      • Secondary Table - Select the secondary table for the join operation.

      • Secondary Table Field - Select the field from the secondary table for the join operation.

      • Join Type - Specify the type of join. Select from the options - inner join and outer join.

      • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data. Choose from the options - 2,4,8.

      • Click Add and then click Save.

      Provide the custom SQL query that you want to run on the data.

      Custom SQL -

      Configure Datastore for Custom SQL

      • Provide the custom SQL query that you want to run on the data.

      • Select the number of parallel connections that you want to use to fetch the data. Choose from the options - 2,4,8.

      • Click Save.

    • Select New Datastore and provide the required information. See Configure Oracle Connection Details.

    • Select Data Ingestion Catalog.

      Configure datastore for data ingestion catalog

      1. Choose a catalog from the list of configured catalogs.

      2. Choose the Catalog Schema.

      3. Select the Tables from the selected schema. If the source schema changes, then you must run the data crawler and create a new catalog to fetch the latest schema.

      4. Click Save.

For more information about Data Catalog, see Data Crawler and Data Catalog

  1. Click the PostgreSQL node.

  2. Use one of the following options to add a datastore:

    • Select Configured Datastore. Choose a datastore from the list of configured datastores. Select one of the following options:

      Select a table from the database from which you want data to be fetched. Click Add.

      Historical and Incremental configuration

      • Click Expand All

      • Configure Historical - If your table contains historical data for a specific period, you can enable this option to fetch data for a specific date range. Do the following:

        • Select the Start Date and End Date to specify the period for which you want the data to be fetched.

        • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

        • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data.

        • Filter - Select the column for which you want to apply the filter.

      • Configure Incremental - If you want to continue fetching data incrementally, you can enable this option.

        • Start Date - Specify the date from which you want to fetch data incrementally.

        • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

        • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data. Choose from the options - 2,4,8.

        • Filter - Specify the name of the column for which you want to apply the filter.

        • Additional Filter - Specify any additional column for which you want to fetch the data.

      This operation combines the rows from two table references, based on join criteria.

      Configure DataStore with Joins option

      Provide the following information under Add Table Joins:

      • Primary Table - Select a primary table for the join operation.

      • Primary Table Field - Select the field from the primary table for the join operation.

      • Secondary Table - Select the secondary table for the join operation.

      • Secondary Table Field - Select the field from the secondary table for the join operation.

      • Join Type - Specify the type of join. Select from the options - inner join and outer join.

      • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data. Choose from the options - 2,4,8.

      • Click Add and then click Save.

      Provide the custom SQL query that you want to run on the data.

      Custom SQL -

      Configure Datastore for Custom SQL

      • Provide the custom SQL query that you want to run on the data.

      • Select the number of parallel connections that you want to use to fetch the data. Choose from the options - 2,4,8.

      • Click Save.

    • Select Data Ingestion Catalog.

      Configure datastore for data ingestion catalog

      1. Choose a catalog from the list of configured catalogs.

      2. Choose the Catalog Schema.

      3. Select the Tables from the selected schema. If the source schema changes, then you must run the data crawler and create a new catalog to fetch the latest schema.

      4. Click Save.

For more information about Data Catalog, see Data Crawler and Data Catalog

  1. Click the Snowflake node.

  2. Use one of the following options to add a datastore:

    • Select Configured Datastore. Choose a datastore from the list of configured datastores. Select one of the following options:

      Select a table from the database from which you want data to be fetched. Click Add.

      Historical and Incremental configuration

      • Click Expand All

      • Configure Historical - If your table contains historical data for a specific period, you can enable this option to fetch data for a specific date range. Do the following:

        • Select the Start Date and End Date to specify the period for which you want the data to be fetched.

        • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

        • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data.

        • Filter - Select the column for which you want to apply the filter.

      • Configure Incremental - If you want to continue fetching data incrementally, you can enable this option.

        • Start Date - Specify the date from which you want to fetch data incrementally.

        • Batch Interval - Specify the number of days for which the data will be fetched in each batch.

        • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data. Choose from the options - 2,4,8.

        • Filter - Specify the name of the column for which you want to apply the filter.

        • Additional Filter - Specify any additional column for which you want to fetch the data.

      This operation combines the rows from two table references, based on join criteria.

      Configure DataStore with Joins option

      Provide the following information under Add Table Joins:

      • Primary Table - Select a primary table for the join operation.

      • Primary Table Field - Select the field from the primary table for the join operation.

      • Secondary Table - Select the secondary table for the join operation.

      • Secondary Table Field - Select the field from the secondary table for the join operation.

      • Join Type - Specify the type of join. Select from the options - inner join and outer join.

      • Parallel Connections - Specify the number of connections that you want running concurrently to fetch the data. Choose from the options - 2,4,8.

      • Click Add and then click Save.

      Provide the custom SQL query that you want to run on the data.

      Custom SQL -

      Configure Datastore for Custom SQL

      • Provide the custom SQL query that you want to run on the data.

      • Select the number of parallel connections that you want to use to fetch the data. Choose from the options - 2,4,8.

      • Click Save.

    • Select New Datastore and provide the required information. See Configuring Snowflake Connection Details.

    • Select Data Ingestion Catalog.

      Configure datastore for data ingestion catalog

      1. Choose a catalog from the list of configured catalogs.

      2. Choose the Catalog Schema.

      3. Select the Tables from the selected schema. If the source schema changes, then you must run the data crawler and create a new catalog to fetch the latest schema.

      4. Click Save.

For more information about Data Catalog, see Data Crawler and Data Catalog

  1. Click the Kinesis Streaming node.

  2. Do one of the following:

    1. Choose Configured Datastore.

      1. Select a datastore from the dropdown.

      2. Click Browse this computer to upload the Kinesis schema in JSON format. Alternately, download the template and then upload the Kinesis schema in JSON format.

    2. Choose New Datastore. See Configuring Amazon Kinesis Data Streams.

 

Related Topics Link IconRecommended Topics What's next? Data Crawler and Data Catalog